﻿Imports System.Data.SqlClient

Public Class SearchItems
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim Str As String = ""
        If TextBox1.Text = "" Then
            Str = "%"
            GridLoad(Str)
        Else
            Str = "%" & TextBox1.Text & "%"
            GridLoad(Str)
        End If
    End Sub

    Protected Sub GridLoad(ByVal Str As String)
        Try
            Dim strConnect As String = ""
            Dim sCon As String = ""
            Dim MyArr As Array
            Dim sErrMsg As String = ""
            strConnect = "SAPConnect"
            sCon = System.Configuration.ConfigurationSettings.AppSettings.Get(strConnect)
            MyArr = sCon.Split(";")
            Dim strSQLconnection As String = "Data Source=" & PublicVariable.oCompany.Server & ";Initial Catalog=" & PublicVariable.oCompany.CompanyDB & ";User Id=" & PublicVariable.oCompany.DbUserName & ";Password=" & MyArr(5).ToString() & ";"
            Dim sqlConnection As New SqlConnection(strSQLconnection)
            Dim sqlCommand As New SqlCommand("SELECT Top 1000 T0.[ItemCode] 'Item Code', T0.[ItemName]'Name', T1.[ItmsGrpNam] 'Group Name', T0.[PrchseItem] 'Purchase Item', T0.[SellItem] 'Sales Item', T0.[InvntItem] 'Inventory Item',T0.[ManBtchNum] 'Manage BatchNum',CONVERT (varchar, T0.[U_AB_CreatedDate],103) [Created Date],CONVERT (varchar, T0.[U_AB_UpdateDate],103) [Update Date], isnull(T0.[U_AB_CreatedBy],'') [Created By],isnull(T0.[U_AB_UpdatedBy],'') [Updated By], isnull(T0.[U_AB_WWCompany],'') [Company] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod where T0.[ItemName] like '" & Str & "'  ", sqlConnection)
        sqlConnection.Open()
            '****************************
            '
            'Select Convert(Of Date, GETDATE())() 'Date Part Only
            'Dim Str1 As String = "SELECT Top 1000 T0.[ItemCode] 'Item Code', T0.[ItemName]'Name', T1.[ItmsGrpNam] 'Group Name', T0.[PrchseItem] 'Purchase Item', T0.[SellItem] 'Sales Item', T0.[InvntItem] 'Inventory Item',T0.[ManBtchNum] 'Manage BatchNum',isnull(T0.[U_AB_CreatedDate],'') [Created Date],isnull(T0.[U_AB_UpdateDate],'') [Update Date], isnull(T0.[U_AB_CreatedBy],'') [Created By],isnull(T0.[U_AB_UpdatedBy],'') [Updated By], isnull(T0.[U_AB_WWCompany],'') [Company] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod where T0.[ItemName] like '" & Str & "'  "
            '' SqlCommand cmd = new SqlCommand(str, con);
            'Dim da As New SqlDataAdapter(Str1, sqlConnection)
            'Dim ds As New DataSet()
            'da.Fill(ds, "0")

            '  SqlDataReader dr = cmd.ExecuteReader();

            'GridView1.DataSource = ds.Tables("0").ToString()
            'GridView1.DataBind()
            'GridView2.BottomPagerRow.Visible = true;


            '***********************************

            Dim reader As SqlDataReader = sqlCommand.ExecuteReader()

            GridView1.DataSource = reader
            GridView1.DataBind()
        Catch ex As Exception
            Label2.Text = ex.Message
        End Try
    End Sub
End Class